<?php

session_start();
/*
 * suDbAdmin.php
 * 在线数据库管理工具,快速简单操作数据库(单页绿色文件,方便部署)
 * 超级命令：
 * 说明：
 * 1.输出模板以单引号括起来,如: list-fields mytable 'drop table {0}\n',模板中单引号需转义[\']
 * 2.?command-prefix 会自动补全命令,如: ?use-table后按回车,则回显use-table <table>
 * 预置命令：
 * 1. use-table <table>: 瞄定数据表,作为默认值供后续操作使用
 * 2. use-database <database>: 瞄定数据库,作为默认值供后续操作使用
 * 3. list-fields [table] [template]:  显示<table>的所有字段名称
 *      @params [table] 数据表名称,为空则使用上次use table <table>指定的表名称,如 mytable, mydb.mytable
 *      @params [template] 输出模板,默认'{field}',占位符包括{field}, {type}, {collation}, {null}, {key}, {default}, {extra}, {privileges}, {comment}
 *      @example
 *          1.list-fields '{0}=>\'{0}\',' 返回field1=>'field1',field2=>'field2',field3=>'field3'
 *          2.list-fields '\n* @params {type} $p{stype}_{name} {comment} 返回* @param int $pint_id 主键编号
 * 4. list-tables [database] [template]: 显示<database>中所有数据表名称
 *      @params [database] 数据库名称,为空则使用默认数据库
 *      @params [template] 输出模板,默认'{table}',占位符包括{database},{table},{type},{engine},{rows},{length},{auto_increment},{crate_time},{collation},{comment}
 *      @example 
 *          1.'drop table {0};\n' 返回 drop table mytable;
 * 5. show-template <table> <short-command> 显示<table>的CURD模板,
 *      @params <short-command> 支持数据表:c(create增)|u(update改)|r(read查)|d(delete删),存储过程:cprocedure|uprocedure|rprocedure|dprocedure,函数:cfunction|ufunction|rfunction|dfunction,视图:cview|uview|rview|dview命令
 *      @example
 *          1.show template u 返回 update mytable set field1='field1'...
 *          2.show template cview 返回 create view ..
 * 6. open -n<linkid> [-t<dbtype> -h<host> -u<uid> -p<pwd> -d[database] -c[charset]]新建或编辑数据库连接(命令执行时即开即关,无需关闭)
 *      @params <linkid> 连接编号,必需唯一,用于关闭连接时使用
 *      @params <dbtype> 数据库类型,暂时只支持MYSQL和MSSQL
 *      @params <host> 服务器地址
 *      @params <uid> 登录名
 *      @params <pwd> 登录密码
 *      @params [database] 数据库名称
 *      @params [charset] 字符集,默认UTF8
 *      @example 
 *          1.open -nmylink -tmysql -hlocalhost -uroot -p -dmydb -cutf8; 打开名称为mylink的连接并放入连接池,如果已存在mylink连接则修改并重新打开
 *          2.open -nmylink 从连接池打开名为mylink的连接(mylink已经以第一种示例方法打开过了)
 * 8. list-connection [-p] 查看连接池中所有连接的信息(包含状态)
 *      @params [-p] 默认不显示密码,输入参数-p则显示密码
 *      @example 
 *          1.list-connection 不显示密码
 *          2.list-connection -p 显示密码
 * 9. theme <name> 设置主题或设置前景色和背景色
 *      @params <name> 预设的主题名称,包含：default|green|orange|blue|black|pink
 * 10. 常用命令：
 *      clear 清屏
 *      date 查看日期
 *      exit 退出
 *      help 帮助
 *      v 版本信息
 * @since 1.0 <2015-5-11> SoChishun Added.
 */

/**
 * 全局预设配置
 * @since 1.0 <2015-5-20> SoChishun Added.
 */
$GLOBAL_CONFIG = array(
// 预设数据库连接节点,以便在open <name>中使用
    'CONNECTION' => array(
        'localhost' => array(
            'DB_TYPE' => 'mysql', // 数据库类型
            'DB_HOST' => 'localhost', // 服务器地址
            'DB_USER' => 'xcall', // 用户名
            'DB_PWD' => 'root!@#xcall098)(*Admin%^&', // 密码 *FD1FF7C8A00F770D0578167BC4BA51A7F236C1F6
            'DB_PORT' => 3306, // 端口
            'DB_NAME' => 'db_xcall', // 数据库名
            'DB_CHARSET' => 'utf8', // 字符集
        )
    ),
    // 预设界面主题节点,以便在theme <name>中使用
    'THEME' => array(
        'default' => array('color' => '#33CC00', 'background-color' => '#000000',),
        'black' => array('color' => '#33CC00', 'background-color' => '#000000',),
        'green' => array('color' => '#FFFFFF', 'background-color' => '#288149',),
        'orange' => array('color' => '#FFFFFF', 'background-color' => '#F26B42',),
        'blue' => array('color' => '#FFFFFF', 'background-color' => '#286CB5',),
        'pink' => array('color' => '#FFFFFF', 'background-color' => '#F287B7',),
        'purple' => array('color' => '#FFDBF7', 'background-color' => '#B95DA4',),
    )
);

$dbadmin = new CSuDbAdmin();
$dbadmin->index();

/**
 * 主控制器类
 * @since 1.0 <2015-5-11> SoChishun Added.
 */
class CSuDbAdmin {

    protected $html_head = '<!DOCTYPE html><html><head><meta charset="UTF-8"><title>SuDbAdmin</title><script src="//code.jquery.com/jquery-1.11.3.min.js"></script><style type="text/css">body{font-size:12px;margin:0px;}</style><script type="text/javascript">jQuery(document).ready(function($){$("textarea").height($(document).height()-20).width($(document).width()-20);});</script></head><body>';
    protected $html_foot = '</body></html>';

    /**
     * 是否Ajax提交
     * @var bool
     */
    protected $is_ajax = false;

    public function __construct() {
        $this->is_ajax = ((isset($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest') || !empty($_POST['ajax']) || !empty($_GET['ajax'])) ? true : false;
    }

    public function index() {
        if ($this->is_ajax) {
            $data = $this->run_cmd(fn_request('cmd'));
            header('Content-Type:application/json; charset=utf-8');
            die(json_encode($data, 0));
        } else {
            die($this->get_html());
        }
    }

    /**
     * 运行命令
     * @param type $cmd
     * @return type
     */
    function run_cmd($cmd) {
        $cmd = trim($cmd);
        if (false !== ($pos = strpos($cmd, ' '))) {
            $pre_cmd = substr($cmd, 0, $pos);
        } else {
            $pre_cmd = $cmd;
        }

        // 超级命令
        $supercmds = array('use-table', 'use-database', 'list-fields', 'list-tables', 'show-template', 'open', 'list-connection');
        if (in_array($pre_cmd, $supercmds)) {
            $m = new MSuperCommand();
            $data = $m->run($cmd, $pre_cmd);
        } else {
            $m = new MDbCommand();
            $data = $m->run($cmd, $pre_cmd);
        }
        return $data;
    }

    function get_html() {
        return $this->html_head . '<div class="black"><textarea cols="120" rows="30"></textarea></div>' . $this->html_foot;
    }

}

/**
 * 超级命令模型类
 * @since 1.0 <2015-5-20> SoChishun Added.
 */
class MSuperCommand {

    /**
     * 执行命令
     * @param type $cmd
     * @param type $pre_cmd
     * @since 1.0 <2015-5-20> SoChishun Added.
     */
    public function run($cmd, $pre_cmd = '') {
        $method_name = str_replace('-', '_', $pre_cmd);
        if (method_exists($this, $method_name)) {
            return $this->$method_name($cmd);
        }
        return fn_ajax_data(false, '命令不支持!');
    }

    /**
     * list-fields [table] [template]
     * @param type $cmd
     * @example list-fields tuser '{table}'
     */
    function list_fields($cmd) {
        $sess_data = fn_get_session();
        if (!$sess_data || !isset($sess_data['CONNS']['CURRENT_CONN'])) {
            return fn_ajax_data(false, '未找到任何连接');
        }
        // 解析命令
        $cmds = fn_cmd_to_array($cmd);
        $cmd_arg = false;
        if (count($cmds) > 1 && $cmds[1]) {
            if (count($cmds) > 2) {
                $table = $cmds[1];
                $cmd_arg = $cmds[2];
            } else {
                if (false === strpos($cmds[1], '{')) {
                    $table = $cmds[1];
                } else {
                    $cmd_arg = $cmds[1];
                }
            }
        }
        // 打开数据库连接
        $conn_info = $sess_data['CONNS']['CURRENT_CONN'];
        if (!isset($table)) {
            if (!isset($sess_data['CONNS']['CURRENT_TABLE'])) {
                return fn_ajax_data(false, '未选择数据表');
            }
            $table = $sess_data['CONNS']['CURRENT_TABLE'];
        }
        $mdb = new MDbCommand($conn_info);
        // 查询
        $msg = $mdb->get_fields($table);
        if (!$cmd_arg) {
            $cmd_arg = "{field}";
        }
        if (!$msg['status']) {
            return $msg;
        }
        $labels = array('{field}' => 'Field', '{type}' => 'Type', '{collation}' => 'Collation', '{null}' => 'Null', '{key}' => 'Key', '{default}' => 'Default', '{extra}' => 'Extra', '{privileges}' => 'Privileges', '{comment}' => 'Comment');
        $results = $msg['info'];
        foreach ($results as $row) {
            $str = $cmd_arg;
            foreach ($labels as $label => $field) {
                $str = str_replace($label, $row[$field], $str);
            }
            $outs[] = $str;
        }
        return fn_ajax_data(true, implode("\n", $outs));
    }

    /**
     * list-tables [database] [template]
     * @param type $cmd
     * @example list-tables 'drop table {table}'
     */
    function list_tables($cmd) {
        $sess_data = fn_get_session();
        if (!$sess_data || !isset($sess_data['CONNS']['CURRENT_CONN'])) {
            return fn_ajax_data(false, '未找到任何连接');
        }
        // 解析命令
        $cmds = fn_cmd_to_array($cmd);
        $cmd_arg = false;
        if (count($cmds) > 1 && $cmds[1]) {
            if (count($cmds) > 2) {
                $db = $cmds[1];
                $cmd_arg = $cmds[2];
            } else {
                if (false === strpos($cmds[1], '{')) {
                    $db = $cmds[1];
                } else {
                    $cmd_arg = $cmds[1];
                }
            }
        }
        // 打开数据库连接
        $conn_info = $sess_data['CONNS']['CURRENT_CONN'];
        if (!isset($db) && !$conn_info['DB_NAME']) {
            return fn_ajax_data(false, '未选择数据库');
        }
        $mdb = new MDbCommand($conn_info);
        // 选择数据库
        if (isset($db) && $db) {
            $msg = $mdb->exec('use ' . $db);
            if (!$msg['status']) {
                return $msg;
            }
        } else {
            $db = $conn_info['DB_NAME'];
        }
        // 查询
        $msg = $mdb->get_tables($db);
        if (!$cmd_arg) {
            $cmd_arg = "{table}";
        }
        if (!$msg['status']) {
            return $msg;
        }
        $labels = array('{database}' => 'TABLE_SCHEMA', '{table}' => 'TABLE_NAME', '{type}' => 'TABLE_TYPE', '{engine}' => 'ENGINE', '{rows}' => 'TABLE_ROWS', '{length}' => 'DATA_LENGTH', '{auto_increment}' => 'AUTO_INCREMENT', '{crate_time}' => 'CREATE_TIME', '{collation}' => 'TABLE_COLLATION', '{comment}' => 'TABLE_COMMENT');
        $results = $msg['info'];
        foreach ($results as $row) {
            $str = $cmd_arg;
            foreach ($labels as $label => $field) {
                $str = str_replace($label, $row[$field], $str);
            }
            $outs[] = $str;
        }
        return fn_ajax_data(true, implode("\n", $outs));
    }

    /**
     * show-template <table> <short-command>
     * @param type $cmd
     */
    function show_template($cmd) {
        
    }

    /**
     * use-table <table>
     * @param type $cmd
     * @since 1.0 <2015-5-20> SoChishun Added.
     * @example use-table mytable
     */
    function use_table($cmd) {
        $cmds = fn_cmd_to_array($cmd);
        if (count($cmds) < 2 || !$cmds[1]) {
            return fn_ajax_data(false, fn_arg_invalid_tip($cmd[0]));
        }
        $sess_data = fn_get_session();
        $sess_data['CONNS']['CURRENT_TABLE'] = $cmds[1];
        $result = fn_set_session($sess_data);
        return fn_ajax_data($result);
    }

    /**
     * 选择数据库
     * @param type $cmd
     * @return type
     * @since 1.0 <2015-5-21> SoChishun Added.
     * @example use-database mydatabase
     */
    function use_database($cmd) {
        $cmds = fn_cmd_to_array($cmd);
        if (count($cmds) < 2 || !$cmds[1]) {
            return fn_ajax_data(false, fn_arg_invalid_tip($cmd[0]));
        }
        $sess_data = fn_get_session();
        if (!$sess_data || !isset($sess_data['CONNS']['CURRENT_CONN'])) {
            return fn_ajax_data(false, '未找到任何连接');
        }
        $current_conn = $sess_data['CONNS']['CURRENT_CONN'];
        $current_conn['DB_NAME'] = $cmds[1];
        $sess_data['CONNS']['CURRENT_CONN'] = $current_conn;
        // 如果有存在连接ID且未定义数据库则自动补上数据库
        if (isset($sess_data['CONNS'][$current_conn['LINK_ID']]) && !$sess_data['CONNS'][$current_conn['LINK_ID']]['DB_NAME']) {
            $sess_data['CONNS'][$current_conn['LINK_ID']]['DB_NAME'] = $cmds[1];
        }
        $result = fn_set_session($sess_data);
        return fn_ajax_data($result);
    }

    /**
     * list-connection
     * @param type $cmd
     * @since 1.0 <2015-5-21> SoChishun Added.
     * @example list-connection -p
     */
    function list_connection($cmd) {
        $sess_data = fn_get_session();
        if (!$sess_data || !isset($sess_data['CONNS']) || !$sess_data['CONNS']) {
            return fn_ajax_data(false, '未找到任何连接');
        }
        $cmds = fn_cmd_to_array($cmd);
        $cmd_arg = false;
        if (count($cmds) > 1 && $cmds[1]) {
            $cmd_arg = $cmds[1];
        }
        $conns = $sess_data['CONNS'];
        $outs[] = false;
        $keys = array('n' => 'LINK_ID', 't' => 'DB_TYPE', 'h' => 'DB_HOST', 'u' => 'DB_USER', 'p' => 'DB_PWD', 'd' => 'DB_NAME', 'c' => 'DB_CHARSET');
        foreach ($conns as $conn) {
            if ($cmd_arg && '-p' == $cmd_arg) {
                $outs[] = sprintf('-n%s -t%s -h%s -u%s -p%s -d%s -c%s', $conn['LINK_ID'], $conn['DB_TYPE'], $conn['DB_HOST'], $conn['DB_USER'], $conn['DB_PWD'], $conn['DB_NAME'], $conn['DB_CHARSET']);
            } else {
                $outs[] = sprintf('-n%s -t%s -h%s -u%s -d%s -c%s', $conn['LINK_ID'], $conn['DB_TYPE'], $conn['DB_HOST'], $conn['DB_USER'], $conn['DB_NAME'], $conn['DB_CHARSET']);
            }
        }
        return fn_ajax_data(true, implode('\n', $outs));
    }

    /**
     * 回显中[C]=Cache,[N]=New
     * open -n<linkid> [-t<dbtype> -h<host> -u<uid> -p<pwd> -d[database] -c[charset]]
     * @param type $cmd
     */
    function open($cmd) {
        $cmds = fn_cmd_to_array($cmd);
        $sess_data = fn_get_session();
        // 从缓存中读取
        if (count($cmds) < 3) {
            global $GLOBAL_CONFIG;
            $conn = isset($sess_data['CONNS'][$cmds[1]]) ? $sess_data['CONNS'][$cmds[1]] : false;
            if (!$conn) {
                $conn = isset($GLOBAL_CONFIG['CONNECTION'][$cmds[1]]) ? $GLOBAL_CONFIG['CONNECTION'][$cmds[1]] : false;
            }
            if (!$conn) {
                return fn_ajax_data(false, '连接 [' . $cmds[1] . '] 不存在[C]');
            }
            $sess_data['CONNS']['CURRENT_CONN'] = $conn;
            $result = fn_set_session($sess_data);
            return false === $result ? fn_ajax_data(false, '系统错误,连接失败') : fn_ajax_data(true, '[-n' . $cmds[1] . ' -t' . $conn['DB_TYPE'] . ' -h' . $conn['DB_HOST'] . ' -u' . $conn['DB_USER'] . ' -d' . $conn['DB_NAME'] . ' -c' . $conn['DB_CHARSET'] . '] 连接成功[C]');
        }
        $keys = array('n' => 'LINK_ID', 't' => 'DB_TYPE', 'h' => 'DB_HOST', 'u' => 'DB_USER', 'p' => 'DB_PWD', 'd' => 'DB_NAME', 'c' => 'DB_CHARSET');
        $config = array('LINK_ID' => '', 'DB_TYPE' => '', 'DB_HOST' => '', 'DB_USER' => '', 'DB_PWD' => '', 'DB_NAME' => '', 'DB_CHARSET' => 'utf8',);
        foreach ($cmds as $arg) {
            $pre = strlen($arg) > 2 && '-' == $arg[0] ? $arg[1] : false;
            if (!$pre) {
                continue;
            }
            $value = substr($arg, 3);
            if ($value && array_key_exists($pre, $keys)) {
                $config[$keys[$pre]] = $value;
            }
        }
        if (!$config['DB_TYPE'] || !$config['LINK_ID'] || !$config['DB_HOST'] || !$config['DB_USER'] || !$config['DB_PWD']) {
            return fn_ajax_data(false, fn_arg_invalid_tip($cmd[0] . '[N]'));
        }
        // 保存到session
        $sess_data['CONNS'][$config['LINK_ID']] = $config;
        $sess_data['CONNS']['CURRENT_CONN'] = $config;
        return fn_ajax_data(true, '[-n' . $config['LINK_ID'] . ' -t' . $config['DB_TYPE'] . ' -h' . $config['DB_HOST'] . ' -u' . $config['DB_USER'] . ' -d' . $config['DB_NAME'] . ' -c' . $config['DB_CHARSET'] . '] 连接成功[N]');
    }

}

/**
 * 数据库命令模型类
 * @since 1.0 <2015-5-20> SoChishun Added.
 */
class MDbCommand {

    public $conn_info;
    public $conn_link;
    public $error;

    public function __construct($conn_info) {
        $this->conn_info = $conn_info;
        $this->conn_link = $this->connect($conn_info);
        if (!is_resource($this->conn_link)) {
            $this->error = $this->conn_link;
            $this->conn_link = null;
        }
    }

    public function __destruct() {
        if (is_resource($this->conn_link)) {
            mysql_close($this->conn_link);
        }
    }

    /**
     * 打开数据库连接
     * @param array $config
     * @return string|mysql_link
     * @since 1.0 <2015-5-21> SoChishun Added.
     */
    public function connect($config) {
        $conn = false;
        if (false === ($conn = @mysql_connect($config['DB_HOST'], $config['DB_USER'], $config['DB_PWD']))) {
            return mysql_error();
        }
        if ($config['DB_NAME'] && false === @mysql_select_db($config['DB_NAME'])) {
            return mysql_error();
        }
        if ($config['DB_CHARSET'] && false === @mysql_query('set names ' . $config['DB_CHARSET'])) {
            return mysql_error();
        }
        return $conn;
    }

    /**
     * 执行数据库命令
     * @param string $sql
     * @param array $conn_info
     * @return array
     * @since 1.0 <2015-5-22> SoChishun Added.
     */
    public function exec($sql, $conn_info = false) {
        if ($conn_info) {
            $link = $this->connect($conn_info);
            if (!is_resource($link)) {
                return fn_ajax_data(false, $link);
            }
        } else {
            $link = $this->conn_link;
        }
        $result = @mysql_query($cmd, $link);
        if (false === $result) {
            return fn_ajax_data(false, mysql_error($link));
        }
        return fn_ajax_data(true, $result);
    }

    /**
     * 
     * @param string $sql
     * @param int $result_type
     * @param array $conn_info
     * @return array
     * @since 1.0 <2015-5-22> SoChishun Added.
     */
    public function query($sql, $result_type = MYSQL_ASSOC, $conn_info = false) {
        if ($conn_info) {
            $link = $this->connect($conn_info);
            if (!is_resource($link)) {
                return fn_ajax_data(false, $link);
            }
        } else {
            $link = $this->conn_link;
        }
        $result = @mysql_query($sql, $link);
        if (false === $result) {
            return fn_ajax_data(false, mysql_error());
        }
        $outs = false;
        while ($row = mysql_fetch_array($result, $result_type)) {
            $outs[] = $row;
        }
        mysql_free_result($result);
        return fn_ajax_data(true, $outs);
    }

    /**
     * 获取所有数据表
     * TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS, DATA_LENGTH, AUTO_INCREMENT, CREATE_TIME, TABLE_COLLATION, TABLE_COMMENT
     * @param type $database
     * @return type
     * @since 1.0 <2015-5-22> SoChishun Added.
     */
    public function get_tables($database) {
        $sql = "select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, `ENGINE`, TABLE_ROWS, DATA_LENGTH, AUTO_INCREMENT, CREATE_TIME, `TABLE_COLLATION`, TABLE_COMMENT  from  information_schema.TABLES where TABLE_TYPE='BASE TABLE' and TABLE_SCHEMA='$database';";
        return $this->query($sql);
    }

    /**
     * 获取数据表的结构数组(包含注释字段)
     * Field, Type, Collation, Null, Key, Default, Extra, Privileges, Comment
     * @param string $table
     * @return array
     * @since 1.0 <2014-8-27> sutroon Added.
     */
    function get_fields($table) {
        return $this->query('SHOW FULL FIELDS FROM ' . $table);
    }

    /**
     * 获取与类型对应的字段值占位符
     * @param array $row 数据行
     * @param bool $fillDefault 填充默认值
     * @return string
     * @since 1.0 2014-8-23 by sutroon
     */
    function get_type_field($row, $fillDefault = false) {
        $type = get_field_alias_type($row['Type']);
        $value = ($row['Null'] == 'NO' && $fillDefault && !is_null($row['Default'])) ? $row['Default'] : $row['Field'];
        if ($value === 'CURRENT_TIMESTAMP') {
            $value = 'now()';
        } else {
            $value = $type == 'str' || $type == 'dat' ? '\'' . $value . '\'' : $value;
        }
        return $value;
    }

    /**
     * 获取与类型对应的字段值
     * @param array $row 数据行
     * @param bool $fillDefault 填充默认值
     * @return string
     * @since 1.0 2014-8-23 by sutroon
     */
    function get_type_value($row, $fillDefault = false) {
        $type = get_field_alias_type($row['Type']);
        $value = '';
        if ($row['Null'] == 'NO') {
            if (is_null($row['Default'])) {
                switch ($type) {
                    case 'dat':
                        $value = '';
                        break;
                    case 'flt':
                        $value = 0.00;
                        break;
                    case 'int':
                        $value = 0;
                        break;
                    default:
                        $value = '';
                        break;
                }
            } else {
                $value = $row['Default'];
            }
            if ($value === 'CURRENT_TIMESTAMP') {
                $value = 'now()';
            } else {
                $value = $type == 'str' || $type == 'dat' ? '\'' . $value . '\'' : $value;
            }
        } else {
            $value = 'NULL';
        }
        return $value;
    }

    /**
     * 判断字段类型的别名
     * @param string $type 数据库字段类型,如int(11),smallint(6)等
     * @return string 返回字段类型的别名,如int,flt,dat,str
     * @since 1.0 2014-9-2 by sutroon
     */
    function get_field_alias_type($type) {
        $arr = array(
            'int' => array('tinyint', 'smallint', 'mediumint', 'int', 'bigint'),
            'flt' => array('float', 'real', 'double', 'decimal', 'numeric'),
            'dat' => array('datetime', 'date', 'timestamp', 'time', 'year')
        );
        $lenType = 0;
        $lenv = 0;
        foreach ($arr as $k => $arrrow) {
            foreach ($arrrow as $v) {
                $lenType = strlen($type);
                $lenv = strlen($v);
                if ($lenType >= $lenv && substr($type, 0, $lenv) == $v) {
                    return $k;
                }
            }
        }
        return 'str';
    }

    /**
     * 获取参数格式的名称
     * @param array $row
     * @param string $varprefix
     * @return string
     * @since 1.0 2014-8-23 by sutroon
     */
    function get_var_field($row, $varprefix = 'p') {
        return $varprefix . get_field_alias_type($row['Type']) . '_' . $row['Field'];
    }

    /**
     * 获取变量注释
     * @param array $row
     * @param string $varprefix
     * @return string
     * @since 1.0 2014-8-23 by sutroon
     */
    function get_var_comment($row, $varprefix = 'p') {
        $type = get_field_alias_type($row['Type']);
        $arr = array('int' => 'int', 'flt' => 'float', 'dat' => 'datetime', 'str' => 'string');
        return '* @param ' . $arr[$type] . ' $' . $varprefix . $type . '_' . $row['Field'] . ' ' . $row['Comment'];
    }

    /**
     * 获取将数据表字段转换为变量名称的文本串
     * @param string $table
     * @since 1.0 2014-8-27 by sutroon
     */
    function get_table_varnames_text($table) {
        $str = '';
        $arr = $this->get_fields($table);
        foreach ($arr as $row) {
            $str .= get_var_field($row) . ', ';
        }
        if ($str) {
            $str = substr($str, 0, strlen($str) - 2);
        }
        return $str;
    }

    /**
     * 获取将数据表自动换转换为变量注释的文本串
     * @param string $table
     * @return string
     * @since 1.0 2014-8-27 by sutroon
     */
    function get_table_varcomments_text($table) {
        $str = '';
        $arr = $this->get_fields($table);
        foreach ($arr as $row) {
            $str .= get_var_comment($row) . PHP_EOL;
        }
        if ($str) {
            $str = substr($str, 0, strlen($str) - 2);
        }
        return $str;
    }

    /**
     * 获取表结构的文本表格
     * @param string $table
     * @return string
     * @since 1.0 2014-8-27 by sutroon
     */
    function get_table_schema_text($table) {
        $str = '<table cellspacing="0"><tr><th>序号</th><th class="th-field">字段定义</th></tr>';
        $i = 1;
        $arr = $this->get_fields($table);
        foreach ($arr as $row) {
            $style = ($i % 2 == 0) ? ' class="tr-alt"' : '';
            $str .= '<tr' . $style . '><td>' . $i . '</td><td>' . get_td_field($row) . '</td></tr>';
            $i++;
        }
        return $str . '</table>';
    }

    /**
     * 显示创建对象的语句
     * 如数据表、视图、存储过程、函数
     * @param $object 对象名称
     * @param string $type 类型，支持TABLE,VIEW,PROCEDURE,FUNCTION
     * @return string
     * @since 1.0 2014-9-26 by sutroon
     * @example get_create_object_sql('proc_test','PROCEDURE');
     */
    function get_create_object_sql($object, $type = 'TABLE') {
        switch (strtolower($type)) {
            case 'view':
                $script = 'Create View';
                break;
            case 'procedure':
                $script = 'Create Procedure';
                break;
            case 'function':
                $script = 'Create Function';
                break;
            default:
                $script = 'Create Table';
                break;
        }
        $result = mysql_query("Show $script $object;");
        $row = mysql_fetch_array($result, MYSQLI_ASSOC);
        if (!isset($row[$script])) {
            die($object . '::' . $type);
        }
        return $result ? $row[$script] : '';
    }

    /**
     * 生成插入脚本
     * @param array $arrschema 表结构数组
     * @return array array(普通的,精简的,默认值的)
     * @since 1.0 2014-8-27 by sutroon
     * @example
     *  $arrout=get_insert_sql('tuser');
     *  echo $arrout[0] . PHP_EOL;
     * echo '=========Lite============' . PHP_EOL;
     * echo $arrout[1] . PHP_EOL;
     * echo '=========Default=========' . PHP_EOL;
     * echo $arrout[2] . PHP_EOL;
     */
    function get_insert_table_sql($table) {
        $str = '';
        $strlite = '';
        $vals = '';
        $valslite = '';
        $valsdefault = '';
        $arr = $this->get_fields($table);
        foreach ($arr as $row) {
            $str .= '`' . $row['Field'] . '`, ';
            $vals .= get_type_field($row) . ', ';
            $valsdefault .= get_type_field($row, true) . ', ';
            if ($row['Null'] == 'NO' && !is_null($row['Default'])) {
                continue;
            }
            $strlite .= '`' . $row['Field'] . '`, ';
            $valslite .= get_type_field($row) . ', ';
        }
        if ($str) {
            $str = substr($str, 0, strlen($str) - 2);
            $vals = substr($vals, 0, strlen($vals) - 2);
            $valsdefault = substr($valsdefault, 0, strlen($valsdefault) - 2);
        }
        if ($strlite) {
            $strlite = substr($strlite, 0, strlen($strlite) - 2);
            $valslite = substr($valslite, 0, strlen($valslite) - 2);
        }
        // 普通的,精简的,默认值的
        return array("insert into $table ($str) values ($vals);", "insert into $table ($strlite) values ($valslite);", "insert into $table ($str) values ($valsdefault);");
    }

    /**
     * 获取查询表的语句
     * @param string $table
     * @return string
     * @since 1.0 2014-8-27 by sutroon
     */
    function get_select_table_sql($table) {
        $str = '';
        $arr = $this->get_fields($table);
        foreach ($arr as $row) {
            $str .= '`' . $row['Field'] . '`, ';
        }
        if ($str) {
            $str = substr($str, 0, strlen($str) - 2);
        }
        return "select $str from $table;";
    }

    /**
     * 获取更新数据表的语句
     * @param string $table
     * @return string
     * @since 1.0 2014-8-27 by sutroon
     */
    function get_update_table_sql($table) {
        $str = '';
        $where = '';
        $i = 0;
        $arr = $this->get_fields($table);
        foreach ($arr as $row) {
            if ($i == 0) {
                $where = '`' . $row['Field'] . '`=' . get_type_field($row);
            } else {
                $str .= '`' . $row['Field'] . '` = ' . get_type_field($row) . ', ';
            }
            $i++;
        }
        if ($str) {
            $str = substr($str, 0, strlen($str) - 2);
        }
        return "update $table set $str where $where;";
    }

    /**
     * 获取更新重置字段的语句
     * @param string $table
     * @return string
     * @since 1.0 2014-9-11 by sutroon
     */
    function get_update_reset_table_sql($table) {
        $str = '';
        $where = '';
        $i = 0;
        $arr = $this->get_fields($table);
        foreach ($arr as $row) {
            if ($i == 0) {
                $where = $row['Field'] . '=' . get_type_value($row);
            } else {
                $str .= $row['Field'] . '=' . get_type_value($row) . ', ';
            }
            $i++;
        }
        if ($str) {
            $str = substr($str, 0, strlen($str) - 2);
        }
        return "update $table set $str where $where;";
    }

    /**
     * 获取从数据表生成创建视图的语句
     * @param string $table
     * @return string
     * @since 1.0 2014-8-27 by sutroon
     */
    function get_create_view_from_table_sql($table) {
        $char = '';
        if (strrpos($table, '_') !== false) {
            $char = substr($table, strrpos($table, '_') + 1, 1);
        } else {
            $char = $table[1];
        }
        $str = '';
        $arr = $this->get_fields($table);
        foreach ($arr as $row) {
            $str .= $char . '.' . $row['Field'] . ', ';
        }
        if ($str) {
            $str = substr($str, 0, strlen($str) - 2);
        }
        return 'create view if not exists v' . substr($table, 1) . ' as' . PHP_EOL . 'create or replace view v' . substr($table, 1) . ' as' . PHP_EOL . "\t" . 'select' . PHP_EOL . "\t" . $str . PHP_EOL . "\t" . "from $table $char;";
    }

}

/**
 * 获取输入参数 支持过滤和默认值
 * 使用方法:
 * <code>
 * fn_request('id',0); 获取id参数 自动判断get或者post
 * fn_request('post.name','','htmlspecialchars'); 获取$_POST['name']
 * fn_request('get.'); 获取$_GET
 * </code>
 * @param string $name 变量的名称 支持指定类型
 * @param mixed $default 不存在的时候默认值
 * @param mixed $filter 参数过滤方法
 * @param mixed $datas 要获取的额外数据源
 * @return mixed
 */
function fn_request($name, $default = '', $filter = null, $datas = null) {
    static $_PUT = null;
    if (strpos($name, '/')) { // 指定修饰符
        list($name, $type) = explode('/', $name, 2);
    } elseif (false) { // 默认强制转换为字符串
        $type = 's';
    }
    if (strpos($name, '.')) { // 指定参数来源
        list($method, $name) = explode('.', $name, 2);
    } else { // 默认为自动判断
        $method = 'param';
    }
    switch (strtolower($method)) {
        case 'get' :
            $input = & $_GET;
            break;
        case 'post' :
            $input = & $_POST;
            break;
        case 'put' :
            if (is_null($_PUT)) {
                parse_str(file_get_contents('php://input'), $_PUT);
            }
            $input = $_PUT;
            break;
        case 'param' :
            switch ($_SERVER['REQUEST_METHOD']) {
                case 'POST':
                    $input = $_POST;
                    break;
                case 'PUT':
                    if (is_null($_PUT)) {
                        parse_str(file_get_contents('php://input'), $_PUT);
                    }
                    $input = $_PUT;
                    break;
                default:
                    $input = $_GET;
            }
            break;
        case 'path' :
            $input = array();
            if (!empty($_SERVER['PATH_INFO'])) {
                $depr = C('URL_PATHINFO_DEPR');
                $input = explode($depr, trim($_SERVER['PATH_INFO'], $depr));
            }
            break;
        case 'request' :
            $input = & $_REQUEST;
            break;
        case 'session' :
            $input = & $_SESSION;
            break;
        case 'cookie' :
            $input = & $_COOKIE;
            break;
        case 'server' :
            $input = & $_SERVER;
            break;
        case 'globals' :
            $input = & $GLOBALS;
            break;
        case 'data' :
            $input = & $datas;
            break;
        default:
            return null;
    }
    if ('' == $name) { // 获取全部变量
        $data = $input;
    } elseif (isset($input[$name])) { // 取值操作
        $data = $input[$name];
    } else { // 变量默认值
        $data = isset($default) ? $default : null;
    }
    return $data;
}

/**
 * 格式化ajax返回值
 * @param boolean $status
 * @param string $info
 * @return array
 * @since 1.0 <2015-5-20> SoChishun Added.
 */
function fn_ajax_data($status, $info = '') {
    return array('status' => $status, 'info' => ($info ? $info : ($status ? '操作成功' : '操作失败')));
}

/**
 * 参数无效提示
 * @param type $cmd
 * @param type $suffix
 * @return type
 * @since 1.0 <2015-5-21> SoChishun Added.
 */
function fn_arg_invalid_tip($cmd, $suffix = '') {
    return '参数无效, 请输入 [?' . $cmd . ' + 回车]查看帮助' . $suffix;
}

/**
 * 返回主session数据
 * @return mixed 成功返回数组,失败返回false
 * @since 1.0 <2015-5-20> SoChishun Added.
 */
function fn_get_session() {
    return isset($_SESSION['sudba']) ? $_SESSION['sudba'] : false;
}

/**
 * 保存主session数据
 * @param array $data
 * @return boolean
 * @since 1.0 <2015-5-20> SoChishun Added.
 */
function fn_set_session($data) {
    $_SESSION['sudba'] = $data;
    return isset($_SESSION['sudba']);
}

/**
 * 字符串cmd转为数组
 * @param string $cmd 命令
 * @param int $limit 数量,索引以1开始,从$limit后不解析
 * @return array
 * @since 1.0 <2015-5-20> SoChishun Added;
 *  2.0 <2015-5-22> SoChishun 重构,支持引号包含的参数(内容包含引号转义符)和$limit参数
 * @example 
 * fn_cmd_to_array('list-tables mydatabase "drop table \\\'{0}\\\'" -h'); 返回 array('list-tables', 'mydatabase', "drop table '{0}'", '-h')
 * fn_cmd_to_array("list-tables mydatabase 'drop table \'{0}\' -h'"); 返回 array('list-tables', 'mydatabase', "drop table '{0}'", '-h')
 * fn_cmd_to_array('list-tables "drop table {0}"')); 返回：array ('list-tables', 'drop table {0}');
 */
function fn_cmd_to_array($cmd, $limit = 0) {
    $cmd = str_replace('\\\'', '\:sq:', trim($cmd));
    $cmd = str_replace('\"', '\:dq:', $cmd);
    $pattern = "/[\'|\"](.*?)[\'|\"]/";
    preg_match_all($pattern, $cmd, $matches);  // 提取引号内容
    $cmd = preg_replace($pattern, '[arg]', $cmd); // 将引号内容转为[arg]占位符
    $cmds = preg_split('/\s+/', $cmd, $limit); // 拆分空格数组
    if ($matches) {
        $n = count($cmds);
        $values = $matches[1];
        foreach ($values as $value) {
            for ($i = 0; $i < $n; $i++) {
                if ('[arg]' == $cmds[$i]) {
                    $cmds[$i] = str_replace('\:dq:', '\"', str_replace('\:sq:', '\'', $value));
                    break;
                }
            }
        }
    }
    return $cmds;
}
